const fs = require('fs');
const path = require('path');
const db = require('./utils/database');

// CSV解析函数
function parseCSV(content) {
    const lines = content.split('\n').filter(line => line.trim());
    const data = [];
    
    for (let i = 1; i < lines.length; i++) { // 跳过标题行
        const line = lines[i].trim();
        if (!line) continue;
        
        // 处理CSV中的逗号分隔，考虑引号内的逗号
        const columns = [];
        let current = '';
        let inQuotes = false;
        
        for (let j = 0; j < line.length; j++) {
            const char = line[j];
            if (char === '"') {
                inQuotes = !inQuotes;
            } else if (char === ',' && !inQuotes) {
                columns.push(current.trim());
                current = '';
            } else {
                current += char;
            }
        }
        columns.push(current.trim()); // 添加最后一列
        
        data.push(columns);
    }
    
    return data;
}

// 导入CSV数据
async function importCSVData() {
    try {
        console.log('开始导入CSV数据...');
        
        // 初始化数据库连接
        await db.init();
        console.log('数据库初始化完成');
        
        // 获取当前目录下的所有CSV文件
        const files = fs.readdirSync('.').filter(file => file.endsWith('.csv'));
        console.log('找到CSV文件:', files);
        
        for (const file of files) {
            const areaName = path.basename(file, '.csv');
            console.log(`\n处理区域: ${areaName}`);
            
            // 检查区域是否已存在
            const existingArea = await db.get('SELECT * FROM areas WHERE name = ?', [areaName]);
            let areaId;
            
            if (existingArea) {
                areaId = existingArea.id;
                console.log(`区域 "${areaName}" 已存在，ID: ${areaId}`);
                // 删除该区域下的所有维修项目
                await db.run('DELETE FROM repair_items WHERE area_id = ?', [areaId]);
                console.log('已清除该区域下的旧数据');
            } else {
                // 创建新区域
                const result = await db.run('INSERT INTO areas (name) VALUES (?)', [areaName]);
                areaId = result.lastID;
                console.log(`创建新区域 "${areaName}"，ID: ${areaId}`);
            }
            
            // 读取CSV文件内容
            const content = fs.readFileSync(file, 'utf-8');
            const data = parseCSV(content);
            
            console.log(`解析到 ${data.length} 条维修项目数据`);
            
            // 导入维修项目数据
            let successCount = 0;
            for (const row of data) {
                try {
                    // 根据CSV格式判断列的含义
                    let name, specification, unit, price, remark;
                    
                    if (row.length >= 6) {
                        // 重庆市育才中学.csv格式: 序号,维修内容,规格型号,数量,单位,结算单价,备注
                        if (file.includes('重庆市育才中学')) {
                            [, name, specification, , unit, price, remark] = row;
                        } 
                        // 两江职业教育中心.csv格式: 序号,规格型号,维修内容,数量,单位,结算单价,备注
                        else {
                            [, specification, name, , unit, price, remark] = row;
                        }
                        
                        // 清理数据
                        name = name?.replace(/"/g, '').trim() || '';
                        specification = specification?.replace(/"/g, '').trim() || '';
                        unit = unit?.replace(/"/g, '').trim() || '次';
                        price = parseFloat(price?.trim()) || 0;
                        remark = remark?.replace(/"/g, '').trim() || '';
                        
                        if (name && price > 0) {
                            await db.run(
                                'INSERT INTO repair_items (area_id, name, specification, unit, price, remark) VALUES (?, ?, ?, ?, ?, ?)',
                                [areaId, name, specification, unit, price, remark]
                            );
                            successCount++;
                        }
                    }
                } catch (error) {
                    console.error(`导入行数据失败:`, row, error.message);
                }
            }
            
            console.log(`成功导入 ${successCount} 条维修项目数据`);
        }
        
        console.log('\nCSV数据导入完成！');
        
        // 显示导入结果统计
        const areaCount = await db.get('SELECT COUNT(*) as count FROM areas');
        const itemCount = await db.get('SELECT COUNT(*) as count FROM repair_items');
        console.log(`\n导入统计:`);
        console.log(`- 区域总数: ${areaCount.count}`);
        console.log(`- 维修项目总数: ${itemCount.count}`);
        
        // 显示各区域的项目数量
        const areaStats = await db.all(`
            SELECT a.name, COUNT(r.id) as item_count 
            FROM areas a 
            LEFT JOIN repair_items r ON a.id = r.area_id 
            GROUP BY a.id, a.name
        `);
        
        console.log('\n各区域项目统计:');
        areaStats.forEach(stat => {
            console.log(`- ${stat.name}: ${stat.item_count} 个项目`);
        });
        
    } catch (error) {
        console.error('导入CSV数据时发生错误:', error);
    }
}

// 执行导入
importCSVData().then(() => {
    console.log('\n导入脚本执行完成');
    process.exit(0);
}).catch(error => {
    console.error('导入脚本执行失败:', error);
    process.exit(1);
});